const mysql = require('./backend/node_modules/mysql2/promise');
const fs = require('fs');
const path = require('path');

(async () => {
  console.log('📤 开始导出本地单词库...\n');
  
  const conn = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'Lycc.2025',
    database: 'fun_english_db'
  });

  // 查询所有单词
  const [words] = await conn.query('SELECT * FROM vocabulary ORDER BY id');
  
  console.log(`✅ 查询到 ${words.length} 个单词\n`);
  
  // 生成SQL插入语句
  let sql = '-- 单词库数据导出\n';
  sql += `-- 导出时间: ${new Date().toLocaleString('zh-CN')}\n`;
  sql += `-- 总单词数: ${words.length}\n\n`;
  
  sql += '-- 禁用外键检查\n';
  sql += 'SET FOREIGN_KEY_CHECKS = 0;\n\n';
  
  sql += '-- 清空现有数据\n';
  sql += 'TRUNCATE TABLE vocabulary;\n\n';
  
  sql += '-- 插入单词数据\n';
  sql += 'INSERT INTO vocabulary (word, phonetic, chinese, grade, unit, image_url, audio_url) VALUES\n';
  
  const values = words.map((word, index) => {
    const w = mysql.escape(word.word);
    const p = mysql.escape(word.phonetic);
    const c = mysql.escape(word.chinese);
    const g = mysql.escape(word.grade);
    const u = mysql.escape(word.unit);
    const img = word.image_url ? mysql.escape(word.image_url) : 'NULL';
    const aud = word.audio_url ? mysql.escape(word.audio_url) : 'NULL';
    
    const line = `(${w}, ${p}, ${c}, ${g}, ${u}, ${img}, ${aud})`;
    return index === words.length - 1 ? line + ';' : line + ',';
  });
  
  sql += values.join('\n');
  sql += '\n\n-- 恢复外键检查\n';
  sql += 'SET FOREIGN_KEY_CHECKS = 1;\n';
  
  // 写入文件
  const filename = 'vocabulary-data.sql';
  fs.writeFileSync(filename, sql, 'utf8');
  
  console.log(`✅ 数据已导出到: ${filename}`);
  console.log(`📊 文件大小: ${(fs.statSync(filename).size / 1024).toFixed(2)} KB\n`);
  
  // 统计
  const [stats] = await conn.query(`
    SELECT grade, COUNT(*) as count 
    FROM vocabulary 
    GROUP BY grade 
    ORDER BY FIELD(grade, '三上', '三下', '四上', '四下', '五上', '五下', '六上', '六下')
  `);
  
  console.log('📋 各年级单词数：');
  stats.forEach(s => console.log(`   ${s.grade}: ${s.count}个`));
  
  console.log('\n🎯 下一步：将 vocabulary-data.sql 上传到服务器并导入\n');
  
  await conn.end();
})();

